﻿using System;
using System.Collections.Generic;
//using System.Linq;
using System.Text;
using System.Data.SqlClient;
using System.Reflection;
using System.Data;

namespace formworks.old
{
    /// <summary>
    /// 完成数据库实体化操作的对象
    /// </summary>
    public class DB
    {

        #region 变量 

        private SqlConnection connect;
        //private string connectionReadServerIp = ".\\SQLEXPRESS";
        //private string connectionReaduser = "sa";
        //private string connectionReadPassword = "sasa";
        //private string connectionReaddatabase = "BookStorage";
        private string connectionString = "";
        
        #endregion

        #region 初始化对象
        public DB()
        {
            connectionString = "server=.\\SQLEXPRESS;uid=root;pwd=123;database=formwork";
            //string.Format("server={0}\\{1};uid={2};pwd={3};database={4}", param);
        }

        public DB(string connectionString)
        {
            this.connectionString = connectionString;
        }
         #endregion

        #region 基础方法

        /// <summary>
        /// 获得一个数据库连接
        /// </summary>
        /// <returns>SqlConnection</returns>
        private SqlConnection getConn()
        {
            try
            {
                //SqlConnection conns = new SqlConnection(connectionString);
                return new SqlConnection(connectionString);
            }
            catch (System.Exception e)
            {
                throw new Exception("获取数据库连接时出错!", e);
            }

            return null;
        }


        /// <summary>
        /// 清除数据库连接
        /// </summary>
        public void clearConn()
        {
            try
            {

                if (null != connect)
                    connect.Close();
                //be test//connect.Dispose();
            }
            catch (System.Exception ex)
            {
                if (connect.State != ConnectionState.Closed)
                    connect.Close();

                throw new Exception("关闭数据库连接时出错!", ex);
            }
            finally
            {
                if (connect.State != ConnectionState.Closed)
                    connect.Close();
            }
        }

        /// <summary>
        /// 返回一个查询命令
        /// </summary>
        /// <param name="sqlStr"></param>
        /// <returns></returns>
        private SqlCommand getCommand(string sqlStr)
        {
            SqlCommand cmd;
            try
            {
                cmd = new SqlCommand();
                connect = getConn();
                connect.Open();

                cmd.Connection = connect;
                cmd.CommandText = sqlStr;

                return cmd;
            }
            catch (Exception ex)
            {
                if (connect.State != ConnectionState.Closed)
                    connect.Close();

                throw new Exception("获取数据库查询命令时出错!请检查数据库服务是否存在.", ex);
            }

            return null;
        }


        /// <summary>
        /// 接受一个查询并返回一个SqlDataReader
        /// </summary>
        /// <param name="sqlStr">SQL查询语句</param>
        /// <returns>返回一个SqlDataReader</returns>
        public SqlDataReader executeQuery(string sqlStr)
        {
            //SqlDataReader dread;
            try
            {
                //SqlCommand cmd = new SqlCommand();
                //connect = getConn();
                //connect.Open();

                //cmd.Connection = connect;
                //cmd.CommandText = sqlStr;
                //dread = cmd.ExecuteReader();

                return this.getCommand(sqlStr).ExecuteReader();
            }
            catch (System.Exception ex)
            {
                if (connect.State != ConnectionState.Closed)
                    connect.Close();
                throw ex;
            }

            return null;
        }

        /// <summary>
        /// 执行一段SQL语句
        /// </summary>
        /// <param name="sqlStr">SQL语句</param>
        /// <returns>操作结果数</returns>
        public int executeNonQuery(string sqlStr)
        {
            //SqlDataReader dread;
            try
            {
                return this.getCommand(sqlStr).ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                if (connect.State != ConnectionState.Closed)
                    connect.Close();
                throw new Exception("无返回集合查询失败！", ex);
            }
            finally
            {
                if (connect.State != ConnectionState.Closed)
                    connect.Close();
            }

            return 0;
        }

        /// <summary>
        /// 获取实体类中标识的主键名称
        /// 例：
        /// getPrimaryKey(propertys[0].GetCustomAttributes(typeof(PrimaryKey), false));
        /// </summary>
        /// <param name="attributes">需要查询的 attribute</param>
        /// <returns>主键那名称</returns>
        private string getPrimaryKey(object[] attributes)
        {
            try
            {
                //string head = "";
                //string tail = "";
                foreach (Constraints item in attributes)
                {
                    if(Constraints.Types.PrimaryKey == item.keyType)
                    {
                        return item.keyValue;
                    }
                    //head = item.primaryKeyType.Substring(0, 3);
                    //tail = item.primaryKeyType.Substring(3,
                    //    item.primaryKeyType.Length - 3);
                    //if ("PK_" == head)
                    //    return tail;
                }

                return null;
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 这个类对可空类型进行判断转换，
        /// 要不然会报错  
        /// </summary>
        /// <param name="value">要检测的值</param>
        /// <param name="conversionType">要变化的类型</param>
        /// <returns></returns>
        private object checkType(object value, Type conversionType)
        {
            if (conversionType.IsGenericType
                && conversionType.GetGenericTypeDefinition().Equals(
                typeof(Nullable<>))
                )
            {
                if (value == null)
                    return null;

                System.ComponentModel.NullableConverter nullableConverter =
                    new System.ComponentModel.NullableConverter(conversionType);
                conversionType = nullableConverter.UnderlyingType;
            }
            return Convert.ChangeType(value, conversionType);
        }

        /// <summary>
        /// 是空或者dbnull
        /// </summary>
        /// <param name="obj">要判定的对象</param>
        /// <returns></returns>
        private bool IsNullOrDBNull(object obj)
        {
            return ((obj is DBNull) || string.IsNullOrEmpty(obj.ToString())) ? true : false;
        }

        /// <summary>
        /// 取得数据库的列对应实体类的属性名  
        /// </summary>
        /// <param name="col">指定的列</param>
        /// <returns></returns>
        //private string getPropertyName(string col)
        //{
        //    col = col.ToLower();
        //    string[] narr = col.Split('_');
        //    col = "";
        //    for (int i = 0; i < narr.Length; i++)
        //    {
        //        if (narr[i].Length > 1)
        //        {
        //            col += narr[i].Substring(0, 1).ToUpper() + narr[i].Substring(1);
        //        }
        //        else
        //        {
        //            col += narr[i].Substring(0, 1).ToUpper();
        //        }
        //    }
        //    return col;
        //}

        #region 实体类到数据库互相转换方法

        /// <summary>
        /// 数据库和实体类的差异转化
        /// 数据库对象 转换到 实体类
        /// 把下斜杠转化为大写
        /// 例: user_name -> userName
        /// </summary>
        /// <param name="fieldName">字段字符串</param>
        /// <returns></returns>
        public string data2entity(string fieldName)
        {
            if (fieldName.IndexOf('_') < 1)
                return fieldName;

            string m_src = fieldName;
            string m_des = "";

            char[] src_char = m_src.ToCharArray();
            for (int i = 0; i < src_char.Length; i++)
            {

                if ('_' == src_char[i])
                {
                    m_des += Char.ToUpper(src_char[i + 1]);
                    ++i;//
                    continue;
                }
                m_des += src_char[i];
            }//end find

            return m_des;
        }

        /// <summary>
        /// 实体类和的数据库差异转化
        /// 实体类 转换到 数据库对象
        /// 把首字母除外的大写转化为下斜杠+小写字符
        /// 例: userName -> user_name
        /// </summary>
        /// <param name="fieldName">字段字符串</param>
        /// <returns></returns>
        public string entity2data(string fieldName)
        {
            string m_src = fieldName;
            //its lower
            if (m_src.ToLower().Equals(fieldName))
                return fieldName;

            string m_des = "";
            char and = '_';

            char[] src_char = m_src.ToCharArray();
            for (int i = 0; i < src_char.Length; i++)
            {
                if (Char.IsUpper(src_char[i]))
                {
                    m_des += and;
                    m_des += Char.ToUpper(src_char[i]);
                    continue;
                }
                m_des += src_char[i];
            }//end find

            return m_des;
        }
        #endregion

        #endregion

        #region 数据库操作方法

        #region 查询操作

        /// <summary>
        /// 查询数据，返回一个指定类型的数据
        /// </summary>
        /// <typeparam name="T">要转换的对象类型</typeparam>
        /// <param name="sqlStr">sql查询语句</param>
        /// <returns>返回一个指定类型的数据</returns>
        public T queryModel<T>(string sqlStr)
        {
            try
            {
                SqlDataReader dr = executeQuery(sqlStr);
                using (dr)
                {
                    if (dr.Read())
                    {
                        Type modelType = typeof(T);
                        T model = Activator.CreateInstance<T>();
                        for (int i = 0; i < dr.FieldCount; i++)
                        {
                            PropertyInfo pi = modelType.GetProperty(
                                data2entity(dr.GetName(i))
                                );//
                            pi.SetValue(model, checkType(dr[i], pi.PropertyType), null);
                        }
                        return model;
                    }
                }
                return default(T);
            }
            catch (Exception ex)
            {
                if (connect.State != ConnectionState.Closed)
                    connect.Close();
                throw ex;
            }
            finally
            {
                if (connect.State != ConnectionState.Closed)
                    connect.Close();
            }
        }

        /// <summary>
        /// 查询数据，返回一个指定类型的数据列表
        /// </summary>
        /// <typeparam name="T">要转换的对象类型</typeparam>
        /// <param name="sqlStr">sql查询语句</param>
        /// <returns>返回一个指定类型的数据列表</returns>
        public List<T> queryList<T>(string sqlStr)
        {
            try
            {

                SqlDataReader dr = executeQuery(sqlStr);
                using (dr)
                {
                    List<T> list = new List<T>();
                    Type modelType = typeof(T);
                    while (dr.Read())
                    {
                        T model = Activator.CreateInstance<T>();
                        for (int i = 0; i < dr.FieldCount; i++)
                        {

                            PropertyInfo property = modelType.GetProperty(
                                data2entity(dr.GetName(i))
                                );//
                            property.SetValue(model, checkType(dr[i], property.PropertyType), null);
                        }
                        list.Add(model);
                    }
                    return list;
                }
            }
            catch(Exception ex)
            {
                if(connect.State != ConnectionState.Closed)
                    connect.Close();
                throw ex;
            }
            finally
            {
                if (connect.State != ConnectionState.Closed)
                    connect.Close();
            }
        }
        
        #endregion

        #region 插入操作

        /// <summary>
        /// 插入一个类型数据到数据库
        /// </summary>
        /// <typeparam name="T">类型名称</typeparam>
        /// <param name="model">要操作的对象</param>
        /// <returns></returns>
        public int insertModel<T>(T model)
        {
            try
            {
                string valueToken = "";
                string tmpFieldNameHead = "";
                string tmpFieldName = "";
                Type modelType = model.GetType();
                PropertyInfo[] propertys = modelType.GetProperties();

                tmpFieldNameHead = entity2data(propertys[0].Name);
                string sqlStr = "INSERT INTO " + modelType.Name + " ("
                    + tmpFieldNameHead;
                for (int i = 1; i < propertys.Length; i++)
                {
                    //                System.out.println( field[i].getName());
                    tmpFieldName = entity2data(propertys[i].Name);
                    sqlStr += "," + tmpFieldName;
                    valueToken += ",@" + tmpFieldName;

                }
                sqlStr += ") VALUES(@" + tmpFieldNameHead + valueToken + ")";

                //
                SqlCommand cmd = getCommand(sqlStr);

                for (int iterValue = 0; iterValue < propertys.Length; iterValue++)
                {
                    cmd.Parameters.Add(
                        new SqlParameter(propertys[iterValue].Name,
                        propertys[iterValue].GetValue(model, null))
                        );
                }

                return cmd.ExecuteNonQuery();

            }
            catch (System.Exception ex)
            {

            }
            finally
            {
            }

            return -1;

        }
        
        #endregion

        #region 更新操作
        /// <summary>
        /// 更新一个类型数据到数据库
        /// </summary>
        /// <typeparam name="T">类型名称</typeparam>
        /// <param name="model">要操作的对象</param>
        /// <returns></returns>
        public int updateModel<T>(T model)
        {
            try
            {
                //string valueToken = "";
                //string tmpFieldNameHead = "";
                string tmpFieldName = "";
                string pkFieldName = "";
                Type modelType = model.GetType();
                PropertyInfo[] propertys = modelType.GetProperties();
                pkFieldName = getPrimaryKey(propertys[0].GetCustomAttributes(
                    typeof(Constraints), false));
                //tmpFieldNameHead = entity2data(propertys[0].Name);

                String sqlStr = "UPDATE " + modelType.Name
                    + " SET ";
                //+ propertys[0].Name 
                //+ "=@" + tmpFieldNameHead;

                //set field sql
                for (int iterField = 1; iterField < propertys.Length; iterField++)
                {
                    if (pkFieldName != propertys[iterField].Name)
                    {
                        tmpFieldName = entity2data(propertys[iterField].Name);
                        sqlStr += tmpFieldName
                            + "=@" + tmpFieldName + ",";
                    }
                }

                sqlStr = sqlStr.Substring(0, sqlStr.Length - 1);
                //构建结束的字符串
                sqlStr += "  WHERE  "
                    + pkFieldName
                    + " = @" + pkFieldName;

                //
                SqlCommand cmd = getCommand(sqlStr);
                SqlParameter pkParam = null;

                for (int iterValue = 0; iterValue < propertys.Length; iterValue++)
                {
                    if (pkFieldName == propertys[iterValue].Name)
                    {
                        pkParam = new SqlParameter(
                            propertys[iterValue].Name,
                            propertys[iterValue].GetValue(model, null));
                    }
                    else
                    {
                        cmd.Parameters.Add(new SqlParameter(
                            propertys[iterValue].Name,
                            propertys[iterValue].GetValue(model, null) 
                            ));
                    }
                }
                //add PK parameter
                cmd.Parameters.Add(pkParam);

                //
                //getPrimaryKey(propertys[0].GetCustomAttributes(typeof(PrimaryKey), false));
                //
                return cmd.ExecuteNonQuery();

            }
            catch (System.Exception ex)
            {
                throw ex;
            }
            finally
            {
            }
            return -1;
        }
        #endregion

        #region 删除操作
        /// <summary>
        /// 删除一个类型数据到数据库
        /// </summary>
        /// <typeparam name="T">类型名称</typeparam>
        /// <param name="model">要操作的对象</param>
        /// <returns></returns>
        public int deleteModel<T>(T model)
        {
            try
            {

                //DELETE FROM * WHERE 
                Type modelType = model.GetType();
                PropertyInfo[] propertys = modelType.GetProperties();
                //
                string pkFieldName = getPrimaryKey(propertys[0].GetCustomAttributes(
                       typeof(Constraints), false));

                string sqlStr = string.Format("DELETE FROM {0} WHERE {1}=@{2}",
                    new string[]
                {
                    modelType.Name,
                    pkFieldName,
                    pkFieldName
                });

                SqlCommand cmd = getCommand(sqlStr);

                for (int iterValue = 0; iterValue < propertys.Length; iterValue++)
                {
                    if (pkFieldName == propertys[iterValue].Name)
                    {
                        cmd.Parameters.Add(new SqlParameter(
                            pkFieldName,
                            propertys[iterValue].GetValue(model, null)
                            ));
                        //
                        break;
                    }
                }
                

                return cmd.ExecuteNonQuery();
            }
            catch (System.Exception ex)
            {
            	throw ex;
            }
            return -1;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="condition"></param>
        /// <returns></returns>
        public int deleteByCondition<T>(string condition)
        {
            try
            {

                T model = Activator.CreateInstance<T>();
                string sqlStr = string.Format("DELETE FROM {0} WHERE {1}",
                        new string[]
                {
                    model.GetType().Name,
                    condition
                });

                return getCommand(sqlStr).ExecuteNonQuery();
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="id"></param>
        /// <returns></returns>
        public int deleteByID<T>(object id)
        {
            try
            {

                T model = Activator.CreateInstance<T>();
                Type modelType = model.GetType();
                string pkFieldName = getPrimaryKey(
                    modelType.GetProperties()[0].GetCustomAttributes(
                   typeof(Constraints), false));

                string sqlStr = string.Format("DELETE FROM {0} WHERE {1}=@{2}",
                        new string[]
                {
                    model.GetType().Name,
                    pkFieldName,
                    pkFieldName
                });

                SqlCommand cmd = getCommand(sqlStr);
                //
                cmd.Parameters.Add(new SqlParameter(pkFieldName, id));

                return cmd.ExecuteNonQuery();
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
        }


        /// <summary>
        /// 
        /// </summary>
        /// <typeparam name="T"></typeparam>
        /// <param name="id"></param>
        /// <returns></returns>
        public int deleteByID<T>(object[] id)
        {
            try
            {                
                T model = Activator.CreateInstance<T>();
                Type modelType = model.GetType();
                string pkFieldName = getPrimaryKey(
                    modelType.GetProperties()[0].GetCustomAttributes(
                   typeof(Constraints), false));

                string sqlStr = string.Format("DELETE FROM {0} WHERE  ",
                    model.GetType().Name);

                for (int iter1 = 0; iter1 < id.Length; iter1++)
                {
                    sqlStr += pkFieldName + "=@"+ pkFieldName + iter1.ToString() + " or ";
                }
                //
                sqlStr = sqlStr.Substring(0, sqlStr.Length - 3);
                SqlCommand cmd = getCommand(sqlStr);
                for (int iter2 = 0; iter2 < id.Length ; iter2++)
                {
                    cmd.Parameters.Add(new SqlParameter(pkFieldName+iter2.ToString(), id[iter2]));	
                }
                
                //
                

                return cmd.ExecuteNonQuery();
            }
            catch (System.Exception ex)
            {
                throw ex;
            }
        }


        #endregion

        #region 事物操作

        public void beginTransaction()
        {
            
        }

        public void rollBack()
        {

        }

        public void commit()
        {

        }
        #endregion

        #endregion


        public DataTable getTables()
        {
            SqlConnection dbconn = getConn();
            string[] restrictions = new string[4]; 
            restrictions[3] = "BASE TABLE";

            dbconn.Open();
            DataTable dt = dbconn.GetSchema("Tables", restrictions);
            dbconn.Close();

            return dt;
        }

        public DataTable getColumns(string tableName)
        {
            string sqlStr = string.Format(
            @"select sys.columns.name, sys.types.name as dataType, sys.columns.max_length as maxLength, sys.columns.is_nullable as isNullAble, 
            (select count(*) from sys.identity_columns where 
	            sys.identity_columns.object_id = sys.columns.object_id 
	            and sys.columns.column_id = sys.identity_columns.column_id ) as isIdentity ,
            (select value from sys.extended_properties where 
	            sys.extended_properties.major_id = sys.columns.object_id 
	            and sys.extended_properties.minor_id = sys.columns.column_id ) as description 
            from sys.columns, sys.tables, sys.types where 
	            sys.columns.object_id = sys.tables.object_id 
	            and sys.columns.system_type_id=sys.types.system_type_id 
	            and sys.tables.name='{0}' and sys.types.name != 'sysname'
            order by sys.columns.column_id", tableName);

            return getDataSet(sqlStr).Tables[0];

        }


        /// <summary>
        /// 获取表约束
        /// </summary>
        /// <param name="tableName"></param>
        /// <returns></returns>
        public DataSet getConstraints(string tableName)
        {
            SqlConnection dbconn = getConn();

            SqlDataAdapter da = 
                new SqlDataAdapter("sp_helpconstraint", 
                   dbconn);
            da.SelectCommand.CommandType = CommandType.StoredProcedure;

            SqlParameter sp = new SqlParameter("@objname", SqlDbType.VarChar);
            sp.Value = tableName;            
            sp.Direction = ParameterDirection.Input;

            SqlParameter rtnval =
                da.SelectCommand.Parameters.Add(sp);
          
            dbconn.Open();

            

            //cmd.CommandType = CommandType.StoredProcedure;

            //SqlParameter rtnval = cmd.Parameters.Add("TB_ProductStorage", SqlDbType.VarChar);
            //rtnval.Direction = ParameterDirection.ReturnValue;


            
            //cmd.ExecuteNonQuery();
            //SqlDataAdapter da = new SqlDataAdapter(cmd);
            DataSet ds = new DataSet();
            da.Fill(ds);//数据放在DataSet中
            //如果是绑定到gridview上
            //GridView1.DataSource = ds.Tables[0].DefaultView;
            //GridView1.DataBind;
            //int tmp = (int)rtnval.Value;//rtnval.Value即为返回值
            dbconn.Close();

            return ds;
        }


        public DataSet getDataSet(string sqlStr)
        {
            SqlConnection dbconn = getConn();
            SqlDataAdapter da = new SqlDataAdapter(sqlStr, dbconn);
            dbconn.Open();
            DataSet ds = new DataSet();
            da.Fill(ds);//数据放在DataSet中
            dbconn.Close();
            return ds;
        }

        
    }//end DB class
}//end namespace
